import pymysql


class DBTools(object):
    # 创建连接
    @classmethod
    def create_conn(cls):
        conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="mengyue", database="jiuchao",
                               charset="utf8")
        return conn

    @classmethod
    def query_one(cls, sql):
        cursor = None
        conn = None
        res = None
        try:
            # 调用创建连接
            conn = DBTools.create_conn()
            # 创建游标
            cursor = conn.cursor()
            # 执行查询语句
            cursor.execute(sql)
            # 提取结果集，返回结果
            res = cursor.fetchone()
        except Exception as err:
            print("执行查询失败:", str(err))
        finally:
            # 关闭游标
            cursor.close()
            # 关闭连接
            conn.close()
            return res

    @classmethod
    def db_uid(cls, sql):
        conn = None
        cursor = None
        try:
            # 调用创建连接
            conn = DBTools.create_conn()
            # 创建游标
            cursor = conn.cursor()
            # 执行增删改语句
            cursor.execute(sql)
            # 打印受影响的行
            print("Affected_rows", conn.affected_rows())
            # 提交事务
            conn.commit()
        except Exception as err:
            # 回滚事务
            conn.rollback()
            print("执行增删改失败:", str(err))
        finally:
            # 关闭游标
            cursor.close()
            # 关闭连接
            conn.close()


# if __name__ == '__main__':
#     # res = DBTools.query_one("select version();")
#     # print("查询结果为:", res)
#     # DBTools.db_uid("UPDATE `jiuchao`.`member_level_sku` SET  `discount` = 97 WHERE `id` = 99;")
